# # database.py
# from sqlalchemy import create_engine
# from sqlalchemy.ext.declarative import declarative_base
# from sqlalchemy.orm import sessionmaker
#
# Base = declarative_base()
#
#
# def get_db_session():
#     # 连接H2数据库
#
#     # 使用H2的JDBC URL格式
#     url = "sqlite:///example.db"
#
#     engine = create_engine(url, echo=True)
#     # 初始化Base，创建所有映射的表（如果不存在的话）
#     Base.metadata.create_all(engine)
#
#     # 创建Session对象，用于与数据库交互
#     Session = sessionmaker(bind=engine)
#     session = Session()
#
#     return session
#
#
# db_session = get_db_session()


# 通过sqlalchemy 连接SQLite数据库 并创建表新增数据和查询数据
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建对象的基类
Base = declarative_base()


# 定义User对象
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    age = Column(Integer)
    email = Column(String(50))


class User_crud:
    def __init__(self):
        self.session = Database().get_session()

    def add_user(self, name, age, email):
        user = User(name=name, age=age, email=email)
        self.session.add(user)
        self.session.commit()


class Database:
    def __init__(self):
        # 连接SQLite数据库
        self.engine = create_engine('sqlite:///example.db')
        # 创建所有表
        Base.metadata.create_all(self.engine)
        # 创建Session对象
        self.Session = sessionmaker(bind=self.engine)

    def get_session(self):
        return self.Session()


if __name__ == '__main__':
    DB = Database()
    session = DB.get_session()

    User_crud().add_user('Alice', 25, 'alice@example.com')

    # # 新增数据
    # user1 = User(name='Alice2', age=25, email='alice@example.com')
    # user2 = User(name='Bob', age=30, email='bob@example.com')
    # session.add_all([user1, user2])
    # session.commit()

    # 根据name删除数据
    # user = session.query(User).filter_by(name='Bob').first()
    # session.delete(user)
    # session.commit()

    # 查询数据
    users = session.query(User).all()

    # for user in users:
    #     session.delete(user)
    #     # print(user.id, user.name, user.age, user.email)

    for user in users:
        print(len(users), user.name, user.age, user.email)

        # 关闭Session
    session.close()
